﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MaxLeaf.Platform.Contract;
using MaxLeaf.Platform.DataContract;
using CommonsLib.Utility;
using System.Xml;
using System.Web;
using CommonsLib.DB;

using System.Data.Common;
using System.Data;
using System.Collections;
using System.Reflection;

using CommonsLib.Settings;
using MaxLeaf.Platform.Functions;
using System.Data.SqlClient;

namespace MaxLeaf.Platform.DALImplementation
{
    public class BaseDAL
    {

        /// <summary>
        /// 不需要更新的字段
        /// </summary>
        public virtual string IgnoreUpdateFields
        {
            get
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// 不能更新的字段
        /// </summary>
        public virtual string[] DoNotUpdateFields
        {
            get
            {
                return new string[] { "is_delete", "posted" };
            }
        }

        
        

        public virtual List<object> SelectByCond(DBAccess dbcon, object model, List<Cond> lstCond, List<SortBy> lstSortBy, List<FieldFilter> lstFieldFilter, ref PageInfo pageInfo)
        {
            string strSql = string.Empty;
            List<object> lst = new List<object>();


            string strTableName = GetTableName(model);
            string strKeyName = DataFunc.GetKeyField(model);
            string strFields = string.Empty;
            bool blnFieldFilter = false;
            if (lstFieldFilter != null && lstFieldFilter.Count > 0)
            {
                blnFieldFilter = true;
                strFields = GetSelectFields(model, lstFieldFilter);
            }
            else
            {
                strFields = DataFunc.GetFields(model);
            }
            if (("," + strFields+",").IndexOf("," + strKeyName + ",", StringComparison.InvariantCultureIgnoreCase) < 0)
            {
                strFields += "," + strKeyName ;
            }
            string strSortBy = GetSortBy(model, lstSortBy);
            if( ("," + strSortBy).IndexOf( "," + strKeyName + " ",  StringComparison.InvariantCultureIgnoreCase ) < 0        )
            {
                strSortBy += "," + strKeyName + " ASC";
            }
            IDataParameter[] dataParams;
            const int TotalCountCol = 9;
            const int TotalPageCountCol = 10;
            if (pageInfo != null && pageInfo.PageSize > 0)
            {


                SqlParameter paramTotalCount = new SqlParameter("@TotalCount", SqlDbType.Int);
                paramTotalCount.Direction = ParameterDirection.Output;
                SqlParameter paramTotalPageCount = new SqlParameter("@TotalPageCount", SqlDbType.Int);
                paramTotalPageCount.Direction = ParameterDirection.Output;
                strSql = "exec P_viewPage @TableName,@FieldList,@PrimaryKey,@Where,@Order,@SortType,@RecorderCount,@PageSize,@PageIndex,@TotalCount Output,@TotalPageCount Output ";
                dataParams = new SqlParameter[] { 
                    new SqlParameter("@TableName",strTableName)
                    , new SqlParameter("@FieldList",strFields)
                    , new SqlParameter("@PrimaryKey",strKeyName)
                    , new SqlParameter("@Where",GetCondForPage(dbcon, model, lstCond))
                    , new SqlParameter("@Order",strSortBy)
                    , new SqlParameter("@SortType","3")
                     , new SqlParameter("@RecorderCount","0")
                     , new SqlParameter("@PageSize",pageInfo.PageSize.ToString())
                     , new SqlParameter("@PageIndex",pageInfo.PageIndex.ToString())

                     , paramTotalCount
                     , paramTotalPageCount
                };
            }
            else
            {

                KeyValuePair<string, IDataParameter[]> kvp = GetCond(dbcon, model, lstCond);
                strSql = string.Format("select {2} from {0} where {1} order by {3}"
                  , strTableName
                  , kvp.Key
                  , strFields
                  , strSortBy);
                dataParams = kvp.Value;
            }


            using (DbDataReader rdr = dbcon.GetDataReader(strSql, dataParams))
            {
                Type type = model.GetType();
                while (rdr.Read())
                {
                    model = System.Activator.CreateInstance(type);
                    DataFunc.Read(model, rdr, blnFieldFilter);
                    lst.Add(model);
                }

            }

            if (pageInfo != null && pageInfo.PageSize > 0)
            {
                pageInfo.TotalCount = CvtFunc.ToInt(dataParams[TotalCountCol].Value);
                pageInfo.TotalPageCount = CvtFunc.ToInt(dataParams[TotalPageCountCol].Value);
            }


            return lst;
        }

        protected virtual string GetCondForPage(DBAccess dbcon, object model, List<Cond> lstCond)
        {
            
            if (lstCond == null || lstCond.Count == 0)
            {
                
                return  "1=1" ;
            }

            string strCond = string.Empty;
            
            foreach (Cond cond in lstCond)
            {
                if (cond.PropName.StartsWith("Func_")) continue;
                if (cond.PropName.StartsWith("UI_")) continue;

                string strFieldName = DataFunc.GetFieldName(model, cond.PropName);
                if (string.IsNullOrEmpty( strFieldName))
                {
                    throw new MaxLeaf.Platform.Commons.MaxLeafException(string.Format("不存在 {0}", cond.PropName));
                }
                strCond += string.Format("{0} {1} '{2}' and "
                    , strFieldName
                    , GetCompare(cond.Compare)
                    ,BaseFunc.SpecialCharDeal( cond.Value.ToString())
                    
                    );
                 
            }

            return CvtFunc.ToLeftString(strCond, strCond.Length - 5);
           
        }

        protected string GetSelectFields(object model, List<FieldFilter> lstFieldFilter)
        {
            string strFields = string.Empty;
            foreach (FieldFilter fieldFilter in lstFieldFilter)
            {
                string strFieldName = DataFunc.GetFieldName(model, fieldFilter.PropName);
                if (string.IsNullOrEmpty(strFieldName))
                {
                    throw new MaxLeaf.Platform.Commons.MaxLeafException(string.Format("不存在 {0}", fieldFilter.PropName));
                }
                strFields += string.Format("{0},", strFieldName);
            }
            return CvtFunc.ToLeftString(strFields, strFields.Length - 1);
        }
        protected string GetSortByFields(object model, List<SortBy> lstSortBy)
        {
            if (lstSortBy == null || lstSortBy.Count == 0)
            {

                return string.Empty;
            }

            string strSortBy = string.Empty;
            foreach (SortBy sort in lstSortBy)
            {
                string strFieldName = DataFunc.GetFieldName(model, sort.PropName);
                if (string.IsNullOrEmpty(strFieldName))
                {
                    throw new MaxLeaf.Platform.Commons.MaxLeafException(string.Format("不存在 {0}", sort.PropName));
                }
                strSortBy += string.Format(",{0}", strFieldName);
            }
            return  strSortBy;
        }

        protected string GetSortBy(object model, List<SortBy> lstSortBy)
        {
            if (lstSortBy==null  || lstSortBy.Count == 0)
            {
                
                return string.Format("{0} ASC",DataFunc.GetKeyField(model));
            }

            string strSortBy = string.Empty;
            foreach(SortBy sort in lstSortBy)
            {
                string strFieldName = DataFunc.GetFieldName(model, sort.PropName);
                if (string.IsNullOrEmpty(strFieldName))
                {
                    throw new MaxLeaf.Platform.Commons.MaxLeafException(string.Format("不存在 {0}", sort.PropName));
                }
                strSortBy += string.Format("{0} {1},", strFieldName, (sort.DESC ? "DESC" : "ASC"));
            }
            return CvtFunc.ToLeftString(strSortBy, strSortBy.Length-1);
        }

        protected virtual KeyValuePair<string, IDataParameter[]> GetCond(DBAccess dbcon, object model, List<Cond> lstCond)
        {
            KeyValuePair<string, IDataParameter[]> kvp ;
            if (lstCond==null || lstCond.Count == 0)
            {
                kvp = dbcon.CreateCondParam(new Dictionary<string, object>() { { string.Empty, string.Empty } });
                return new KeyValuePair<string,IDataParameter[]>("1=1",kvp.Value);
            }

            string strCond = string.Empty;
            Dictionary<string, object> dictCond=new Dictionary<string, object>();
            foreach (Cond cond in lstCond)
            {
                if (cond.PropName.StartsWith("Func_")) continue;
                if (cond.PropName.StartsWith("UI_")) continue;

                string strFieldName = DataFunc.GetFieldName(model, cond.PropName);
                if (string.IsNullOrEmpty(strFieldName))
                {
                    throw new MaxLeaf.Platform.Commons.MaxLeafException(string.Format("不存在 {0}", cond.PropName));
                }
                string strParamName=GetParamName(strFieldName,cond.Compare);
                strCond += string.Format("{0} {1} {2}{3} and "
                    , strFieldName
                    , GetCompare(cond.Compare)
                    , dbcon.ParamSign
                    , strParamName
                    );
                if (cond.Value == null)
                {
                    throw new MaxLeaf.Platform.Commons.MaxLeafException(string.Format("{0} 值为 null", cond.PropName));
                }
                dictCond.Add(strParamName,cond.Value);
            }
            if (dictCond == null || dictCond.Count == 0)
            {
                kvp = dbcon.CreateCondParam(new Dictionary<string, object>() { { string.Empty, string.Empty } });
                return new KeyValuePair<string, IDataParameter[]>("1=1", kvp.Value);
            }
            kvp = dbcon.CreateCondParam(dictCond);
            return new KeyValuePair<string, IDataParameter[]>(CvtFunc.ToLeftString(strCond, strCond.Length - 5), kvp.Value);
        }

        private string  GetParamName(string strFieldName,CompareType compareType)
        {
            return string.Format("{0}_{1}",strFieldName,compareType);
        }

        public static string GetCompare(CompareType compareType)
        {
            switch(compareType){
                case CompareType.Equal :
                    return "=";
                case CompareType.Greater:
                    return ">";
                case CompareType.GreaterEqual:
                    return ">=";
                case CompareType.Less:
                    return "<";
                case CompareType.LessEqual:
                    return "<=";
                case CompareType.Like:
                    return " like ";
                case CompareType.NotEqual:
                    return "<>";
                case CompareType.NotLike:
                    return "not  like ";
                default:
                    throw new MaxLeaf.Platform.Commons.MaxLeafException("Wroong CompareType");
            };
        }
        public virtual object Add(DBAccess dbcon, object model)
        {
            string strTableName = GetTableName(model);
            string strKeyName = DataFunc.GetKeyField(model);
            string strFields = DataFunc.GetFields(model);


            object[] datas = DataFunc.GetValues(model, strFields);

            KeyValuePair<string, IDataParameter[]> kvp = dbcon.CreateParamSql(strFields, strTableName, datas, DBAccess.LogStatus_Add, strKeyName, null);
            string strIdentity = dbcon.GetValue(kvp.Key, kvp.Value);
            DataFunc.SetIdentityValue(model, strIdentity);
            if (DataFunc.ExistField(model, "recstamp"))
            {
                DataFunc.SetValue(model, DataFunc.GetDBValue(dbcon, model, "recstamp"), "recstamp", true);
            }

            return model;
        }


        public virtual object Update(DBAccess dbcon, object model)
        {
             
            string strTableName = GetTableName(model);
            string strKeyName = DataFunc.GetKeyField(model);
            string strFields = DataFunc.GetFields(model);
            object objKeyFieldValue = DataFunc.GetKeyValue(model);


            object[] datas = DataFunc.GetValues(model, strFields);

            SetIgnore(strFields, datas, GetIgnoreUpdateFields(model));


            
            KeyValuePair<string, IDataParameter[]> kvp = dbcon.CreateParamSql(strFields, strTableName, datas, DBAccess.LogStatus_Update, strKeyName, objKeyFieldValue);
            dbcon.ExecuteSql(kvp.Key, kvp.Value);
            if (DataFunc.ExistField(model, "recstamp"))
            {
                DataFunc.SetValue(model, DataFunc.GetDBValue(dbcon, model, "recstamp"), "recstamp",true);
            }

            return model;
        }

        private string GetIgnoreUpdateFields(object model)
        {
            string strFields = this.IgnoreUpdateFields;
            foreach (string strDoNot in DoNotUpdateFields)
            {
                if (strFields.IndexOf(strDoNot, StringComparison.InvariantCultureIgnoreCase)<0
                    && DataFunc.ExistField(model, strDoNot))
                {
                    if (string.IsNullOrEmpty(strFields))
                    {
                        strFields = strDoNot;
                    }
                    else
                    {
                        strFields = string.Format("{0},{1}", strFields, strDoNot);
                    }
                }
            }
            return strFields;
        }

        private void SetIgnore(string strFields, object[] datas,string strIgnoreUpdateFields )
        {
           
            if (strIgnoreUpdateFields.Length > 0)
            {
                string[] arrIgnoreUpdateFields = strIgnoreUpdateFields.Split(',');
                string[] arrFields = strFields.Split(',');

                for (int j = 0; j < arrIgnoreUpdateFields.Length; j++)
                {
                    for (int i = 0; i < arrFields.Length; i++)
                    {
                        if (arrFields[i].Equals(arrIgnoreUpdateFields[j], StringComparison.InvariantCultureIgnoreCase))
                        {
                            datas[i] = null;
                            break;
                        }
                    }
                }
            }

            
        }

         

        public virtual bool Delete(DBAccess dbcon, object model)
        {
             
            string strTableName = GetTableName(model);
            string strKeyName = DataFunc.GetKeyField(model);
            string strFields = DataFunc.GetFields(model);
            object objKeyValue = DataFunc.GetKeyValue(model);


            Dictionary<string, object> dictCond = new Dictionary<string, object>() { { strKeyName, objKeyValue } };
            KeyValuePair<string, IDataParameter[]> kvp = dbcon.CreateCondParam(dictCond);
            string strSql = string.Format(@"delete from {0} where  {1}", strTableName, kvp.Key);
            dbcon.ExecuteSql(strSql, kvp.Value);



            return true;
        }
         
        /// <summary>
        /// 假删除，设is_delete=1
        /// </summary>
        /// <param name="dbcon"></param>
        /// <param name="model"></param>
        public void FakeDelete(DBAccess dbcon, object model)
        {
            string strTableName = GetTableName(model);
            string strKeyName = DataFunc.GetKeyField(model);            
            object objKeyValue = DataFunc.GetKeyValue(model);


            Dictionary<string, object> dictCond = new Dictionary<string, object>() { { strKeyName, objKeyValue } };
            KeyValuePair<string, IDataParameter[]> kvp = dbcon.CreateCondParam(dictCond);
            string strSql = string.Format(@"update   {0} set is_delete=1 where  {1}", strTableName, kvp.Key);
            dbcon.ExecuteSql(strSql, kvp.Value);
        }


        
        public void FakeUndelete(DBAccess dbcon, object model)
        {
            string strTableName = GetTableName(model);
            string strKeyName = DataFunc.GetKeyField(model);
            object objKeyValue = DataFunc.GetKeyValue(model);


            Dictionary<string, object> dictCond = new Dictionary<string, object>() { { strKeyName, objKeyValue } };
            KeyValuePair<string, IDataParameter[]> kvp = dbcon.CreateCondParam(dictCond);
            string strSql = string.Format(@"update   {0} set is_delete=0 where  {1}", strTableName, kvp.Key);
            dbcon.ExecuteSql(strSql, kvp.Value);
        }

        /// <summary>
        /// 验证字段唯一
        /// </summary>
        /// <param name="dbcon"></param>
        /// <param name="model"></param>
        /// <param name="arrField"></param>
        /// <returns></returns>
        public bool CheckUnique(DBAccess dbcon, object model, string[] arrField)
        {
            Dictionary<string, object> dictCond = new Dictionary<string, object>() ;
            foreach (string strField in arrField)
            {
                dictCond.Add(strField, DataFunc.GetValue(model, strField));
            }

            KeyValuePair<string, IDataParameter[]> kvp = dbcon.CreateCondParam(dictCond);

            string strTableName = GetTableName(model);
            string strKeyName = DataFunc.GetKeyField(model);            
            object objKeyValue = DataFunc.GetKeyValue(model);
                        
            List<IDataParameter> list =new List<IDataParameter>( kvp.Value);
            
            Dictionary<string, object> dictKeyCond = new Dictionary<string, object>() { { strKeyName, objKeyValue } };
            KeyValuePair<string, IDataParameter[]> kvpKey = dbcon.CreateCondParam(dictKeyCond);
            list.AddRange(kvpKey.Value);

            string strCond = kvp.Key + string.Format(" and {0}<>@{0}", strKeyName);
            string strSql =string.Format( "select {2} from {0}  where {1}",strTableName,strCond,strKeyName);

            using (DbDataReader rdr = dbcon.GetDataReader(strSql, list.ToArray()))
            {
                if (rdr.Read())
                {
                    return false;
                }
                else
                {
                    return true;
                }

            }
        }

        public object InsertFinterID(DBAccess dbcon, object model, string strFinterIDFields)
        {
            string strTableName = GetTableName(model);
            string strKeyName = DataFunc.GetKeyField(model);
            string strFields =strFinterIDFields;
            if (!string.IsNullOrEmpty(this.IgnoreUpdateFields))
            {
                strFields=string.Format("{0},{1}",strFields,this.IgnoreUpdateFields);
            }


            object[] datas = DataFunc.GetValues(model, strFields);

            KeyValuePair<string, IDataParameter[]> kvp = dbcon.CreateParamSql(strFields, strTableName, datas, DBAccess.LogStatus_Add, strKeyName, null);
            string strIdentity = dbcon.GetValue(kvp.Key, kvp.Value);
            DataFunc.SetIdentityValue(model, strIdentity);
            if (DataFunc.ExistField(model, "recstamp"))
            {
                DataFunc.SetValue(model, DataFunc.GetDBValue(dbcon, model, "recstamp"), "recstamp", true);
            }

            return model;
        }

        public virtual string GetTableName(object model)
        {
            return DataFunc.GetTableName(model);
        }

        public virtual List<double> Sum(DBAccess dbcon, object model, List<Cond> lstCond, List<SumInfo> lstSumInfo)
        {

            string strSql = string.Empty;
            List<double> lst = new List<double>();


            string strTableName = GetTableName(model);
            string strKeyName = DataFunc.GetKeyField(model);
            string strFields = string.Empty;

            foreach (SumInfo info in lstSumInfo)
            {
                if(strFields.Length==0)
                {
                    strFields = string.Format("{0}({1})", GetSumOperate(info.SumType), GetSumField(model, info));
                }
                else
                {
                    strFields += string.Format(",{0}({1})", GetSumOperate(info.SumType), GetSumField(model, info));
                }
            }
            IDataParameter[] dataParams;
            

                KeyValuePair<string, IDataParameter[]> kvp = GetCond(dbcon, model, lstCond);
                strSql = string.Format("select {2} from {0} where {1}"
                  , strTableName
                  , kvp.Key
                  , strFields
                  );
                dataParams = kvp.Value;
            


            using (DbDataReader rdr = dbcon.GetDataReader(strSql, dataParams))
            {
                Type type = model.GetType();
                while (rdr.Read())
                {
                    for (int i = 0; i < lstSumInfo.Count;i++ )
                    {
                        lst.Add(CvtFunc.ToDouble( rdr[i]));
                    }
                    
                }

            }

             


            return lst;
        }

        private   string GetSumField(object model, SumInfo info)
        {
            string strFieldName= DataFunc.GetFieldName(model, info.PropName);
            if (info.SumType == SumInfoType.CountDistinct)
            {
                return string.Format("Distinct({0})", strFieldName);
            }

            return strFieldName;
        }

        private string GetSumOperate(SumInfoType sumInfoType)
        {
            switch (sumInfoType)
            {
                case SumInfoType.CountDistinct :
                    return "Count";
                case SumInfoType.Sum:
                    return "Sum";
            }

            throw new MaxLeaf.Platform.Commons.MaxLeafException(string.Format("unknown SumInfoType {0}", sumInfoType));
        }

        public virtual bool CheckCanPost(DBAccess dbcon, string strTableName, string strKeyName, int intKey)
        {
            KeyValuePair<string, IDataParameter[]> kvp = dbcon.CreateCondParam(new Dictionary<string, object>() { 
                     { strKeyName, intKey } 
                });

            string strSql = string.Format("select posted,is_delete from {0} where {1}", strTableName, kvp.Key);
            using (DbDataReader rdr = dbcon.GetDataReader(strSql, kvp.Value))
            {
                if (!rdr.Read()) return false;
                if (rdr.GetBoolean(0)) return false;
                if (rdr.GetBoolean(1)) return false;
            }
            return true;
        }



        public object GetFieldValue(DBAccess dbcon, object model, int intKey, string strFieldName)
        {
            string strTableName = GetTableName(model);
            string strKeyName = DataFunc.GetKeyField(model);
            KeyValuePair<string, IDataParameter[]> kvp = dbcon.CreateCondParam(new Dictionary<string, object>() { 
                     { strKeyName, intKey } 
                });

            string strSql = string.Format("select {2} from {0} where {1}", strTableName, kvp.Key, strFieldName);
            using (DbDataReader rdr = dbcon.GetDataReader(strSql, kvp.Value))
            {
                if (!rdr.Read()) return null;
                return rdr.GetValue(0);
 
            }
            return null;
        }



        public static string GetBaseDatabase()
        {
            return string.Format("{0}.dbo.", DataFunc.GetDBName(typeof(ImInvMasterDataModel)));
        }

        public static string GetOrdDatabase()
        {
            return string.Format("{0}.dbo.", DataFunc.GetDBName(typeof(imStoresReqHdrDataModel)));
        }

        public static string GetInvDatabase()
        {
            return string.Format("{0}.dbo.", DataFunc.GetDBName(typeof(ImInvLocDataModel)));
        }
    }


}
